/* QAD_CurrentSurplusInventory.p - Current Surplus Inventory                  */
/* Copyright 1986-2010 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_TransactionsAccountingReport.p 1046 2010-09-10 09:08:19Z jvz  $: */
/*                                                                            */
/* Report matches legacy ppptrp21.p for use by QAD Reporting Framework.       */
/*                                                                            */

{mfsubdirs.i}
{{&us_bbi}mfdeclre.i}
{{&us_bbi}gplabel.i}
{{&us_px}pxmaint.i}
{{&us_px}pxphdef.i ppitxr}
{{&us_px}pxphdef.i cscsxr}
{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}


/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/*
   field TEMP-TABLE DEFINITION BLOCK */
define temp-table ttProductline no-undo
   field line      like pt_mstr.pt_prod_line
   field site      like ld_det.ld_site
   field qty_x_sum like in_mstr.in_qty_oh
   field qty_1_sum like in_mstr.in_qty_oh
   field val_x_sum like glt_det.glt_amt
   field val_1_sum like glt_det.glt_amt
   field unique_id as   integer
   field icount    as   integer
.

define temp-table ttItem no-undo
   field item_num          like pt_mstr.pt_part
   field descrption    like pt_mstr.pt_desc1
   field issue_date    like in_mstr.in_iss_date
   field pm_code       like pt_mstr.pt_pm_code
   field stat          like pt_mstr.pt_status
   field qty_x         like in_mstr.in_qty_oh
   field qty_1         like in_mstr.in_qty_oh
   field val_x         like glt_det.glt_amt
   field val_1         like glt_det.glt_amt
   field ref_unique_id like ttProductline.unique_id
.

define temp-table ttReportConditions no-undo
   field pt_abc    like pt_mstr.pt_abc
   field ld_loc    like ld_det.ld_loc
   field ld_status like ld_det.ld_status
.

define temp-table ttReportOptions no-undo
   field date1   like in_mstr.in_iss_date
   field costset like cs_mstr.cs_set
   field summary like mfc_ctrl.mfc_logical
.

define dataset dsReportResults for ttProductline, ttItem, ttReportConditions, ttReportOptions.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input parameter runReport    as logical no-undo.
define input parameter reportHandle as handle  no-undo.
define input parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

define variable default_cost_set like cs_mstr.cs_set no-undo.
define variable cost_user_input like cs_mstr.cs_set  no-undo.
{com/qad/shell/report/reporting.i}
{com/qad/shell/report/reports/QAD_HelperFunctions.i}  
define variable vhDS as handle    no-undo.

empty temp-table ttProductline   no-error.
empty temp-table ttItem   no-error.
empty temp-table ttReportConditions   no-error.
empty temp-table ttReportOptions no-error.

{{&us_px}pxrun.i &PROC  = 'getStandardCostSet'
    &PROGRAM = 'cscsxr.p'
    &HANDLE=ph_cscsxr
    &PARAM = "  (input '',
                 input '',
                 output default_cost_set)"
    &NOAPPERROR = true
    &CATCHERROR = true
}

for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_CurrentSurplusInventory.meta.xml").

   run SetReportMetaData (input reportHandle).
 
   cost_user_input = GetFilterValue("ttReportOptions","costset",default_cost_set).
   if not can-find(first cs_mstr where cs_mstr.cs_domain = global_domain and
      cs_mstr.cs_set_isparent = no and cs_set = cost_user_input) then
   do:

      /*Invalid Child Cost Set*/
      {us/bbi/pxmsg.i &MSGNUM=25208 &ERRORLEVEL=3}
      return.
   end.
   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.
end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.

   run SetFieldMetaParameter in ipReportHandle(
         "ttReportOptions", "costset", "DefaultValue", default_cost_set).

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
         "summary", "FieldLabel", getLabel("SUMMARY/DETAIL")).

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
         "summary", "DataFormat", getLabel("SUMMARY/DETAIL")).
   

end.

/* DATA RETRIEVING LOGIC BLOCK */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* RETRIEVE THE DATA FROM DATABASE */
   define variable queryString   as character no-undo.
   define variable hMainQuery    as handle    no-undo.
   define query    MainQuery     for ld_det,in_mstr,is_mstr,pt_mstr,si_mstr.
   define variable is_summary    like mfc_ctrl.mfc_logical no-undo.
   define variable issdate_temp  as date initial ? no-undo. 
   define variable qty_x_temp    like in_mstr.in_qty_oh no-undo.
   define variable qty_1_temp    like qty_x_temp no-undo.   
   define variable val_x_temp    like glt_det.glt_amt no-undo.
   define variable val_1_temp    like val_x_temp no-undo.
   define variable date1_temp    like in_mstr.in_iss_date initial ? no-undo.
   define variable unique_id_temp like ttProductline.unique_id initial 0 no-undo.
   define variable qty_x_sum_temp like qty_x_temp no-undo.
   define variable qty_1_sum_temp like qty_1_temp no-undo.
   define variable val_x_sum_temp like val_x_temp no-undo.
   define variable val_1_sum_temp like val_1_temp no-undo.
   define variable icount_temp    like  ttProductline.icount no-undo.

   assign
      qty_x_temp           = 0
      qty_1_temp           = 0
      val_x_temp           = 0
      val_1_temp           = 0
      icount_temp          = 0
      is_summary           = IsTrueValue("ttReportOptions","summary",false)
      date1_temp           = GetDateFilterValue("ttReportOptions","date1",?) 
      hMainQuery           = query MainQuery:handle.

   queryString = "for each ld_det no-lock where ld_domain = "
                  + quoter(global_domain).  
   run AddSpecificConditions in reportHandle
      ("pt_mstr", "pt_part:ld_part", input-output queryString).
   run AddAllConditions in reportHandle
      ("ld_det", input-output queryString).
   
   queryString = queryString + ",".
     
   queryString = queryString + " each in_mstr no-lock where in_domain = "
                  + quoter(global_domain)
                  + " and in_part = ld_part "
                  + " and in_site = ld_site "
                  + ",".

   queryString = queryString + " each is_mstr no-lock where is_domain = "
                  + quoter(global_domain)
                  + " and is_status = ld_status "
                  + ",".

   queryString = queryString + " each pt_mstr no-lock where pt_domain = "
                  + quoter(global_domain)
                  + " and pt_part = ld_part ".
   run AddSomeConditions in reportHandle
      ("pt_mstr", "pt_part,pt_abc", input-output queryString).
   queryString = queryString + ",".

   queryString = queryString + " each si_mstr no-lock where si_domain = "
                  + quoter(global_domain)
                  + " and si_site = in_site".
   


   queryString = queryString + " break by ld_det.ld_site by pt_mstr.pt_prod_line
   by ld_det.ld_part : ".

   Message queryString.

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().

   repeat while not hMainQuery:query-off-end:
      {us/px/pxrun.i &PROC  = 'readInventoryDetail' &PROGRAM = 'ppitxr.p'
                  &HANDLE=ph_ppitxr
                  &PARAM = "(input  pt_mstr.oid_pt_mstr,
                             input  si_mstr.oid_si_mstr,
                             buffer pti_det)"
                  &NOAPPERROR = true
                  &CATCHERROR = true
         }
         
      
      if available pti_det then
         if ValuateInputCharacter("pti_abc","ttReportConditions","pt_abc")
         then
         .
         else
            hMainQuery:get-next().
      else
         if not IsConditionEmpty("ttReportConditions","pt_abc") then
            hMainQuery:get-next().
   


      if hMainQuery:first-of(2)  
         then do:
            /*clear the temp sum value*/
            qty_x_sum_temp = 0.
            qty_1_sum_temp = 0.
            val_x_sum_temp = 0.
            val_1_sum_temp = 0.
            icount_temp = 0.
            
      end.
            

         /*FIND DATE OF LAST ISSUE*/
      if hMainQuery:first-of(3) 
         then do:
         issdate_temp = ?.
         find last tr_hist
            where tr_domain = global_domain
            and (tr_part = ld_det.ld_part
            and (tr_type = "ISS-SO" or tr_type = "ISS-COR" or tr_type = "ISS-WO"
            or tr_type = "ISS-UNP" or tr_type = "ISS-FAS")
            and tr_site =ld_det.ld_site)
            use-index tr_part_eff no-lock no-error.
         if available tr_hist then 
            issdate_temp = tr_effdate.
         else do:
            if in_iss_date <> ? then issdate_temp = in_iss_date.
            else if in_rec_date <= date1_temp
               then issdate_temp = in_rec_date.
         end.
      end.
      /*FIND DATE OF LAST ISSUE END*/      

      if ld_expire <> ? and ld_expire < today
         then qty_x_temp = qty_x_temp + ld_qty_oh.
      else if issdate_temp <> ? and issdate_temp <= date1_temp
         then qty_1_temp = qty_1_temp + ld_qty_oh.


      if hMainQuery:last-of(3) and (qty_x_temp <> 0 or qty_1_temp <> 0)
         then do:
        
         /*FIND UNIT COST TO USE*/
         assign
            glxcst = 0
            curcst = 0.

         for first sct_det no-lock where
            sct_det.sct_domain = global_domain
            and sct_sim = cost_user_input
            and sct_part = in_part
            and sct_site = in_site:
            assign
               glxcst = sct_cst_tot
               curcst = sct_cst_tot.
         end. /* */

         /*CALCULATE INVENTORY VALUES*/
         assign
            val_x_temp = qty_x_temp * glxcst
            val_1_temp = qty_1_temp * glxcst.

         if not is_summary
            then do:
            create ttItem. 
            assign 
               ttItem.item_num = pt_mstr.pt_part
               ttItem.descrption= pt_mstr.pt_desc1
               ttItem.issue_date = issdate_temp
               ttItem.pm_code = pt_mstr.pt_pm_code
               ttItem.stat = pt_mstr.pt_status
               ttItem.qty_x = qty_x_temp
               ttItem.qty_1 = qty_1_temp
               ttItem.val_x = val_x_temp
               ttItem.val_1 = val_1_temp
               ttItem.ref_unique_id = unique_id_temp
               icount_temp = icount_temp + 1.
         end.
         
         /*Accumulate sum value*/
         qty_x_sum_temp = qty_x_sum_temp + qty_x_temp.
         qty_1_sum_temp = qty_1_sum_temp + qty_1_temp.
         val_x_sum_temp = val_x_sum_temp + val_x_temp.
         val_1_sum_temp = val_1_sum_temp + val_1_temp.

         assign
         /*clear temp value*/
            qty_x_temp = 0
            qty_1_temp = 0
           /* val_x_temp = 0
            val_1_temp = 0*/
            .
            
      end. /*last-of end*/
      

      if hMainQuery:last-of(2)
            then do:
            create ttProductline.
            assign
               ttProductline.site = ld_det.ld_site
               ttProductline.line = pt_mstr.pt_prod_line.
               ttProductline.unique_id = unique_id_temp.
               ttProductline.qty_x_sum = qty_x_sum_temp.
               ttProductline.qty_1_sum = qty_1_sum_temp.
               ttProductline.val_x_sum = val_x_sum_temp.
               ttProductline.val_1_sum = val_1_sum_temp.
               ttProductline.icount = icount_temp.
               unique_id_temp = unique_id_temp + 1.
      end.      

      hMainQuery:get-next().
   end. /*query end*/

   hMainQuery:query-close().
   delete object hMainQuery.
   phReportResults = dataset dsReportResults:handle.
END PROCEDURE.
